-- 路径规划路网数据库表结构
-- 每个国家独立的路网表，统一字段结构

-- 创建PostGIS扩展
CREATE EXTENSION IF NOT EXISTS postgis;

-- 国家配置表
CREATE TABLE routing_countries (
    country_code VARCHAR(3) PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL,
    table_name VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 中国路网表
CREATE TABLE china_routing_network (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    geom GEOMETRY(LINESTRING, 4326) NOT NULL,
    length DOUBLE PRECISION,
    source BIGINT,
    target BIGINT,
    source_point GEOMETRY(POINT, 4326),
    target_point GEOMETRY(POINT, 4326),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 台湾路网表
CREATE TABLE taiwan_routing_network (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    geom GEOMETRY(LINESTRING, 4326) NOT NULL,
    length DOUBLE PRECISION,
    source BIGINT,
    target BIGINT,
    source_point GEOMETRY(POINT, 4326),
    target_point GEOMETRY(POINT, 4326),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 美国路网表
CREATE TABLE usa_routing_network (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    geom GEOMETRY(LINESTRING, 4326) NOT NULL,
    length DOUBLE PRECISION,
    source BIGINT,
    target BIGINT,
    source_point GEOMETRY(POINT, 4326),
    target_point GEOMETRY(POINT, 4326),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 德国路网表
CREATE TABLE germany_routing_network (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    geom GEOMETRY(LINESTRING, 4326) NOT NULL,
    length DOUBLE PRECISION,
    source BIGINT,
    target BIGINT,
    source_point GEOMETRY(POINT, 4326),
    target_point GEOMETRY(POINT, 4326),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 日本路网表
CREATE TABLE japan_routing_network (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    geom GEOMETRY(LINESTRING, 4326) NOT NULL,
    length DOUBLE PRECISION,
    source BIGINT,
    target BIGINT,
    source_point GEOMETRY(POINT, 4326),
    target_point GEOMETRY(POINT, 4326),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 韩国路网表
CREATE TABLE korea_routing_network (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    geom GEOMETRY(LINESTRING, 4326) NOT NULL,
    length DOUBLE PRECISION,
    source BIGINT,
    target BIGINT,
    source_point GEOMETRY(POINT, 4326),
    target_point GEOMETRY(POINT, 4326),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 为中国路网表创建索引
CREATE INDEX idx_china_routing_geom ON china_routing_network USING GIST (geom);
CREATE INDEX idx_china_routing_source_point ON china_routing_network USING GIST (source_point);
CREATE INDEX idx_china_routing_target_point ON china_routing_network USING GIST (target_point);
CREATE INDEX idx_china_routing_source ON china_routing_network (source);
CREATE INDEX idx_china_routing_target ON china_routing_network (target);
CREATE INDEX idx_china_routing_name ON china_routing_network (name);

-- 为台湾路网表创建索引
CREATE INDEX idx_taiwan_routing_geom ON taiwan_routing_network USING GIST (geom);
CREATE INDEX idx_taiwan_routing_source_point ON taiwan_routing_network USING GIST (source_point);
CREATE INDEX idx_taiwan_routing_target_point ON taiwan_routing_network USING GIST (target_point);
CREATE INDEX idx_taiwan_routing_source ON taiwan_routing_network (source);
CREATE INDEX idx_taiwan_routing_target ON taiwan_routing_network (target);
CREATE INDEX idx_taiwan_routing_name ON taiwan_routing_network (name);

-- 为美国路网表创建索引
CREATE INDEX idx_usa_routing_geom ON usa_routing_network USING GIST (geom);
CREATE INDEX idx_usa_routing_source_point ON usa_routing_network USING GIST (source_point);
CREATE INDEX idx_usa_routing_target_point ON usa_routing_network USING GIST (target_point);
CREATE INDEX idx_usa_routing_source ON usa_routing_network (source);
CREATE INDEX idx_usa_routing_target ON usa_routing_network (target);
CREATE INDEX idx_usa_routing_name ON usa_routing_network (name);

-- 为德国路网表创建索引
CREATE INDEX idx_germany_routing_geom ON germany_routing_network USING GIST (geom);
CREATE INDEX idx_germany_routing_source_point ON germany_routing_network USING GIST (source_point);
CREATE INDEX idx_germany_routing_target_point ON germany_routing_network USING GIST (target_point);
CREATE INDEX idx_germany_routing_source ON germany_routing_network (source);
CREATE INDEX idx_germany_routing_target ON germany_routing_network (target);
CREATE INDEX idx_germany_routing_name ON germany_routing_network (name);

-- 为日本路网表创建索引
CREATE INDEX idx_japan_routing_geom ON japan_routing_network USING GIST (geom);
CREATE INDEX idx_japan_routing_source_point ON japan_routing_network USING GIST (source_point);
CREATE INDEX idx_japan_routing_target_point ON japan_routing_network USING GIST (target_point);
CREATE INDEX idx_japan_routing_source ON japan_routing_network (source);
CREATE INDEX idx_japan_routing_target ON japan_routing_network (target);
CREATE INDEX idx_japan_routing_name ON japan_routing_network (name);

-- 为韩国路网表创建索引
CREATE INDEX idx_korea_routing_geom ON korea_routing_network USING GIST (geom);
CREATE INDEX idx_korea_routing_source_point ON korea_routing_network USING GIST (source_point);
CREATE INDEX idx_korea_routing_target_point ON korea_routing_network USING GIST (target_point);
CREATE INDEX idx_korea_routing_source ON korea_routing_network (source);
CREATE INDEX idx_korea_routing_target ON korea_routing_network (target);
CREATE INDEX idx_korea_routing_name ON korea_routing_network (name);

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为所有路网表创建更新时间触发器
CREATE TRIGGER update_china_routing_updated_at 
    BEFORE UPDATE ON china_routing_network 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_taiwan_routing_updated_at 
    BEFORE UPDATE ON taiwan_routing_network 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_usa_routing_updated_at 
    BEFORE UPDATE ON usa_routing_network 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_germany_routing_updated_at 
    BEFORE UPDATE ON germany_routing_network 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_japan_routing_updated_at 
    BEFORE UPDATE ON japan_routing_network 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_korea_routing_updated_at 
    BEFORE UPDATE ON korea_routing_network 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建查找最近节点的函数
CREATE OR REPLACE FUNCTION find_nearest_node(
    input_geom GEOMETRY,
    input_country VARCHAR(3),
    search_radius DOUBLE PRECISION DEFAULT 1000.0
)
RETURNS BIGINT AS $$
DECLARE
    nearest_node_id BIGINT;
    table_name VARCHAR(50);
BEGIN
    -- 根据国家代码选择表名
    table_name := CASE input_country
        WHEN 'CHN' THEN 'china_routing_network'
        WHEN 'TWN' THEN 'taiwan_routing_network'
        WHEN 'USA' THEN 'usa_routing_network'
        WHEN 'DEU' THEN 'germany_routing_network'
        WHEN 'JPN' THEN 'japan_routing_network'
        WHEN 'KOR' THEN 'korea_routing_network'
        ELSE NULL
    END;
    
    IF table_name IS NULL THEN
        RETURN NULL;
    END IF;
    
    -- 动态查询最近节点
    EXECUTE format('SELECT id FROM %I WHERE ST_DWithin(geom, $1, $2) ORDER BY ST_Distance(geom, $1) LIMIT 1', table_name)
    USING input_geom, search_radius
    INTO nearest_node_id;
    
    RETURN nearest_node_id;
END;
$$ LANGUAGE plpgsql;

-- 创建计算几何距离的函数
CREATE OR REPLACE FUNCTION calculate_geometry_distance(
    geom1 GEOMETRY,
    geom2 GEOMETRY
)
RETURNS DOUBLE PRECISION AS $$
BEGIN
    RETURN ST_Distance(geom1, geom2) * 111000; -- 粗略转换为米
END;
$$ LANGUAGE plpgsql;

-- 插入国家配置数据
INSERT INTO routing_countries (country_code, country_name, table_name) VALUES
('CHN', '中国', 'china_routing_network'),
('TWN', '台湾', 'taiwan_routing_network'),
('USA', '美国', 'usa_routing_network'),
('DEU', '德国', 'germany_routing_network'),
('JPN', '日本', 'japan_routing_network'),
('KOR', '韩国', 'korea_routing_network');

-- 插入示例路网数据
INSERT INTO china_routing_network (name, geom, length, source, target, source_point, target_point) VALUES
('京沪高速', ST_GeomFromText('LINESTRING(116.4074 39.9042, 121.4737 31.2304)', 4326), 1200.5, 1, 2, 
 ST_GeomFromText('POINT(116.4074 39.9042)', 4326), ST_GeomFromText('POINT(121.4737 31.2304)', 4326)),
('京广高速', ST_GeomFromText('LINESTRING(116.4074 39.9042, 113.2644 23.1291)', 4326), 2100.8, 1, 3,
 ST_GeomFromText('POINT(116.4074 39.9042)', 4326), ST_GeomFromText('POINT(113.2644 23.1291)', 4326));

INSERT INTO taiwan_routing_network (name, geom, length, source, target, source_point, target_point) VALUES
('國道一號', ST_GeomFromText('LINESTRING(121.5654 25.0330, 120.2130 22.9969)', 4326), 372.7, 4, 5,
 ST_GeomFromText('POINT(121.5654 25.0330)', 4326), ST_GeomFromText('POINT(120.2130 22.9969)', 4326));

INSERT INTO usa_routing_network (name, geom, length, source, target, source_point, target_point) VALUES
('Interstate 95', ST_GeomFromText('LINESTRING(-74.0060 40.7128, -80.1918 25.7617)', 4326), 1895.0, 6, 7,
 ST_GeomFromText('POINT(-74.0060 40.7128)', 4326), ST_GeomFromText('POINT(-80.1918 25.7617)', 4326));

-- 创建视图：按国家分组的路网统计
CREATE VIEW routing_network_stats AS
SELECT 
    rc.country_code,
    rc.country_name,
    rc.table_name,
    CASE rc.country_code
        WHEN 'CHN' THEN (SELECT COUNT(*) FROM china_routing_network)
        WHEN 'TWN' THEN (SELECT COUNT(*) FROM taiwan_routing_network)
        WHEN 'USA' THEN (SELECT COUNT(*) FROM usa_routing_network)
        WHEN 'DEU' THEN (SELECT COUNT(*) FROM germany_routing_network)
        WHEN 'JPN' THEN (SELECT COUNT(*) FROM japan_routing_network)
        WHEN 'KOR' THEN (SELECT COUNT(*) FROM korea_routing_network)
        ELSE 0
    END as total_segments,
    CASE rc.country_code
        WHEN 'CHN' THEN (SELECT COALESCE(SUM(length), 0) FROM china_routing_network)
        WHEN 'TWN' THEN (SELECT COALESCE(SUM(length), 0) FROM taiwan_routing_network)
        WHEN 'USA' THEN (SELECT COALESCE(SUM(length), 0) FROM usa_routing_network)
        WHEN 'DEU' THEN (SELECT COALESCE(SUM(length), 0) FROM germany_routing_network)
        WHEN 'JPN' THEN (SELECT COALESCE(SUM(length), 0) FROM japan_routing_network)
        WHEN 'KOR' THEN (SELECT COALESCE(SUM(length), 0) FROM korea_routing_network)
        ELSE 0
    END as total_length
FROM routing_countries rc
WHERE rc.is_active = true;

-- 添加注释
COMMENT ON TABLE routing_countries IS '国家配置表 - 管理支持的国家和地区';
COMMENT ON COLUMN routing_countries.country_code IS '国家代码（ISO 3166-1 alpha-3）';
COMMENT ON COLUMN routing_countries.country_name IS '国家名称';
COMMENT ON COLUMN routing_countries.table_name IS '对应的路网表名';
COMMENT ON COLUMN routing_countries.is_active IS '是否激活';

COMMENT ON TABLE china_routing_network IS '中国路网数据表';
COMMENT ON TABLE taiwan_routing_network IS '台湾路网数据表';
COMMENT ON TABLE usa_routing_network IS '美国路网数据表';
COMMENT ON TABLE germany_routing_network IS '德国路网数据表';
COMMENT ON TABLE japan_routing_network IS '日本路网数据表';
COMMENT ON TABLE korea_routing_network IS '韩国路网数据表';

COMMENT ON COLUMN china_routing_network.name IS '道路名称';
COMMENT ON COLUMN china_routing_network.geom IS '道路几何信息（线几何）';
COMMENT ON COLUMN china_routing_network.length IS '道路长度（米）';
COMMENT ON COLUMN china_routing_network.source IS '起点节点ID';
COMMENT ON COLUMN china_routing_network.target IS '终点节点ID';
COMMENT ON COLUMN china_routing_network.source_point IS '起点坐标';
COMMENT ON COLUMN china_routing_network.target_point IS '终点坐标';
